package com.vecspace.msoffice;


import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

public class POIUtil {

	
	/**读取单元格数据
	 * 
	 * @param cell
	 * @return 
	 * @author: wanghua
	 * @reviewer: 
	 */
	public static Object getCellValue(Cell cell){
        if(cell == null) return null;
        CellType cellType = cell.getCellType();
        Object val = null;
        if(CellType.STRING == cellType) {
    		val = cell.getStringCellValue();
    	}else if(CellType.NUMERIC == cellType) {
//    		comment by yuehan
//     		double doubleVal = cell.getNumericCellValue();
//     		long longVal = Math.round(doubleVal);
//     		if(Math.abs(doubleVal - longVal) < 0.0000001){   //判断是否含有小数位.0
//     			val = longVal;
//     		}
//     		else{
//     			val = doubleVal;
//     		}
    		//add by yuehan
     		if (DateUtil.isCellDateFormatted(cell)) {
     			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 
     			Date date = cell.getDateCellValue();
     			val = sdf.format(date);
            }
            else {
            	double doubleVal = cell.getNumericCellValue();
         		long longVal = Math.round(doubleVal);
         		if(Math.abs(doubleVal - longVal) < 0.0000001){   //判断是否含有小数位.0
         			val = longVal;
         		}
         		else{
         			val = doubleVal;
         		}
            }
//    		val = cell.getNumericCellValue();
    	}else if(CellType.BOOLEAN == cellType) {
    		val = cell.getBooleanCellValue();
    	}else if(cell.getCellType() == CellType.FORMULA) {
    		CellType cachedFormulaResultType = cell.getCachedFormulaResultType();
        	if(CellType.STRING == cachedFormulaResultType) {
        		val = cell.getStringCellValue();
        	}else if(CellType.NUMERIC == cachedFormulaResultType) {
        		val = cell.getNumericCellValue();
        	}else if(CellType.BOOLEAN == cachedFormulaResultType) {
        		val = cell.getBooleanCellValue();
        	}
        }
        return val;
    }
	
	/**读取单元格数据
	 * 
	 * @param cell
	 * @return 
	 * @author: wanghua
	 */
	public static String getCellText(Cell cell){
		Object value = getCellValue(cell);
		if(value == null)return null;
		return value.toString();
	}
	/**
	 * 根据单元格别名获取AreaReference
	 * @param wb
	 * @param name
	 * @return 
	 * @author: 
	 */
	public static AreaReference GetRangeAreaReference(Workbook wb, String name) {
		Name aNamedCell = wb.getName(name); 
		if(aNamedCell == null)
			return null;
		return new AreaReference(aNamedCell.getRefersToFormula(), null);
	} 
	
	/**
	 * 根据AreaReference获取单元格对象二维数组
	 * @param wb
	 * @param aref
	 * @return 
	 * @author: 
	 */
	public static Cell[][] getRangeCellsArry(Workbook wb, AreaReference aref) {
		Cell[][] result;
        if(aref.isSingleCell()) {
        	result = new Cell[1][1];
        	CellReference ref =  aref.getFirstCell();
        	Sheet s = wb.getSheet(ref.getSheetName());
	        Row r = s.getRow(ref.getRow());
	        Cell c = r.getCell(ref.getCol()); 
	        result[0][0] = c;
        	return result;
        }
 
        int minRow = Math.min(aref.getFirstCell().getRow(), aref.getLastCell().getRow());
        int maxRow = Math.max(aref.getFirstCell().getRow(), aref.getLastCell().getRow());
        int minCol = Math.min(aref.getFirstCell().getCol(), aref.getLastCell().getCol());
        int maxCol = Math.max(aref.getFirstCell().getCol(), aref.getLastCell().getCol());
        String sheetName = aref.getFirstCell().getSheetName();
        result = new Cell[maxRow-minRow+1][maxCol-minCol+1];
        
        int i = 0;
        int j = 0;
        Sheet s = wb.getSheet(sheetName);
        for(int row=minRow; row<=maxRow; row++) {
            for(int col=minCol; col<=maxCol; col++) {
                CellReference ref = new CellReference(sheetName, row, col, aref.getFirstCell().isRowAbsolute(), aref.getFirstCell().isColAbsolute());
		        Row r = s.getRow(ref.getRow());
		        Cell c = r.getCell(ref.getCol()); 
                result[i][j] = c;
                j++;
            }
            i++;
            j=0;
        }
        return result;
    }
}
